<!DOCTYPE html>
<html>
<head hexo-theme='https://volantis.js.org/#'>
  <meta charset="utf-8">
  <!-- SEO相关 -->
  
    
  
  <!-- 渲染优化 -->
  <meta name="renderer" content="webkit">
  <meta name="force-rendering" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
  <meta name="HandheldFriendly" content="True" >
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="baidu-site-verification" content="hZiKlANZ5f" />
  <!-- 页面元数据 -->
  
    <title>MySQL笔记(1):终端基本操作 - CyChan&#39;s Blog</title>
  
    <meta name="keywords" content="MySQL">
  
  
    <meta name="description" content="一.登录和退出数据库服务器
登录:$ mysql -uroot -p123456  
退出:exit;">
  

  <!-- feed -->
  
    <link rel="alternate" href="/atom.xml" title="CyChan's Blog">
  

  <!-- import meta -->
  

  <!-- link -->
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13/css/all.min.css">
  
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.css">

  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-waves@0.7.6/dist/waves.min.css">

  

  
  <link rel="shortcut icon" type='image/x-icon' href="https://gitee.com/CyChan811/BlogImage/raw/master/img/头像.jpg">
  

  
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/aplayer@1.10/dist/APlayer.min.css">
  

  

  <!-- import link -->
  

  
  
    
<link rel="stylesheet" href="/css/style.css">

  

  <script>
    function setLoadingBarProgress(num) {
      document.getElementById('loading-bar').style.width=num+"%";
    }
  </script>

  
  
</head>

<body>
  
  <div id="loading-bar-wrapper">
  <div id="loading-bar"></div>
</div>
<header class="l_header shadow blur">
  <div class='container'>
  <div class='wrapper'>
    <div class='nav-sub'>
      <p class="title"></p>
      <ul class='switcher nav-list-h'>
        <li><a class="s-comment fas fa-comments fa-fw" target="_self" href='javascript:void(0)'></a></li>
        
          <li><a class="s-toc fas fa-list fa-fw" target="_self" href='javascript:void(0)'></a></li>
        
      </ul>
    </div>
		<div class="nav-main">
      
        
        <a class="title flat-box" target="_self" href='/'>
          
          
          
            CyChan
          
          
        </a>
      

			<div class='menu navigation'>
				<ul class='nav-list-h'>
          
          
          
            
            
              <li>
                <a class="flat-box" href=/
                  
                  
                  
                    id="home"
                  >
                  <i class='fas fa-home fa-fw'></i>首页
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/categories/
                  
                  
                  
                    id="categories"
                  >
                  <i class='fas fa-folder-open fa-fw'></i>分类
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/tags/
                  
                  
                  
                    id="tags"
                  >
                  <i class='fas fa-tags fa-fw'></i>标签
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/archives/
                  
                  
                  
                    id="archives"
                  >
                  <i class='fas fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/friends/
                  
                  
                  
                    id="friends"
                  >
                  <i class='fas fa-link fa-fw'></i>友链
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/Message/
                  
                  
                  
                    id="Message"
                  >
                  <i class='fas fa-comments fa-fw fa-fw'></i>留言板
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/about/
                  
                  
                  
                    id="about"
                  >
                  <i class='fas fa-info-circle fa-fw'></i>关于
                </a>
                
              </li>
            
          
          
				</ul>
			</div>

      <div class="m_search">
        <form name="searchform" class="form u-search-form">
          <i class="icon fas fa-search fa-fw"></i>
          <input type="text" class="input u-search-input" placeholder="Search..." />
        </form>
      </div>

			<ul class='switcher nav-list-h'>
				
					<li><a class="s-search fas fa-search fa-fw" target="_self" href='javascript:void(0)'></a></li>
				
				<li>
          <a class="s-menu fas fa-bars fa-fw" target="_self" href='javascript:void(0)'></a>
          <ul class="menu-phone list-v navigation white-box">
            
              
            
              <li>
                <a class="flat-box" href=/
                  
                  
                  
                    id="home"
                  >
                  <i class='fas fa-home fa-fw'></i>首页
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/categories/
                  
                  
                  
                    id="categories"
                  >
                  <i class='fas fa-folder-open fa-fw'></i>分类
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/tags/
                  
                  
                  
                    id="tags"
                  >
                  <i class='fas fa-tags fa-fw'></i>标签
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/archives/
                  
                  
                  
                    id="archives"
                  >
                  <i class='fas fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/friends/
                  
                  
                  
                    id="friends"
                  >
                  <i class='fas fa-link fa-fw'></i>友链
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/Message/
                  
                  
                  
                    id="Message"
                  >
                  <i class='fas fa-comments fa-fw fa-fw'></i>留言板
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/about/
                  
                  
                  
                    id="about"
                  >
                  <i class='fas fa-info-circle fa-fw'></i>关于
                </a>
                
              </li>
            
          
            
          </ul>
        </li>
			</ul>
		</div>
	</div>
  </div>
</header>

<script>setLoadingBarProgress(40);</script>



  <div class="l_body nocover">
    <div class='body-wrapper'>
      

<div class='l_main'>
  

  
    <article id="post" class="post white-box reveal shadow article-type-post" itemscope itemprop="blogPost">
      


  <section class='meta'>
    
      
      
      <div class="meta" id="header-meta">
        
          
  <h1 class="title">
    <a href="/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/">
      MySQL笔记(1):终端基本操作
    </a>
  </h1>


        
        <div class='new-meta-box'>
          
            
          
            
              
<div class='new-meta-item author'>
  <a href="https://cychan811.gitee.io" rel="nofollow">
    <img src="https://gitee.com/CyChan811/BlogImage/raw/master/img/头像.jpg">
    <p>CyChan</p>
  </a>
</div>

            
          
            
              
  
  <div class='new-meta-item category'>
    <a href='/categories/%E5%90%8E%E5%8F%B0/' rel="nofollow">
      <i class="fas fa-folder-open fa-fw" aria-hidden="true"></i>
      <p>后台</p>
    </a>
  </div>


            
          
            
              <div class="new-meta-item date">
  <a class='notlink'>
    <i class="fas fa-calendar-alt fa-fw" aria-hidden="true"></i>
    <p>发布于：2020年7月4日</p>
  </a>
</div>

            
          
            
              

            
          
            
              
  <div class="new-meta-item browse valine">
    <a class='notlink'>
      <i class="fas fa-eye fa-fw" aria-hidden="true"></i>
      
      <span id="/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/" class="leancloud_visitors" data-flag-title="MySQL笔记(1):终端基本操作">
      <p>
        <span class="leancloud-visitors-count"></span>
      </p>
      </span>
    </a>
  </div>


            
          
        </div>
        
          <hr>
        
      </div>
    
  </section>


      <section class="article typo">
        <div class="article-entry" itemprop="articleBody">
          
          
          <h2 id="一-登录和退出数据库服务器"><a href="#一-登录和退出数据库服务器" class="headerlink" title="一.登录和退出数据库服务器"></a>一.登录和退出数据库服务器</h2><ul>
<li>登录:<br><code>$ mysql -uroot -p123456</code>  </li>
<li>退出:<br><code>exit;</code><a id="more"></a>

</li>
</ul>
<h2 id="二-基本语法"><a href="#二-基本语法" class="headerlink" title="二.基本语法"></a>二.基本语法</h2><p>– 显示所有数据库<br><code>show databases;</code></p>
<p>– 创建数据库<br><code>create database test;</code></p>
<p>– 选择需要操作的数据库<br><code>use test;</code></p>
<p>– 显示数据库中的所有数据表<br><code>show tables;</code></p>
<hr>
<p>– 创建数据表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> pet(</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    owner <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    species <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    sex <span class="built_in">char</span>(<span class="number">1</span>),</span><br><span class="line">    birth <span class="built_in">date</span>,</span><br><span class="line">    death <span class="built_in">date</span>);</span><br></pre></td></tr></table></figure>
<p><strong>注意事项:</strong> </p>
<ol>
<li>var()与varchar()的区别在于var()是定长的,哪怕存储的字符串没有达到”()”中数字的上限,var()依然会占用空格来填充空间,而varchar()是不定长的,没有达到”()”中的上限则会自动去掉后面的空格;</li>
<li>定义最后一个字段的时候不要加”,”;</li>
</ol>
<hr>
<p>– 查看创建好的数据表的结构<br>– describe pet;<br><code>desc pet;</code><br>说明:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">+-------+-------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type        | Null | Key | Default | Extra |</span><br><span class="line">+-------+-------------+------+-----+---------+-------+</span><br></pre></td></tr></table></figure>
<p>Field: 字段的名称<br>Type: 字段的类型,可以有int var varchar<br>Key: 是否是关键字,如可以定义为 primary key<br>Default: 若该字段没有主动设置值的时候,该字段的默认值是什么</p>
<hr>
<p>– 查看数据表中的数据<br><code>select * from pet;</code></p>
<hr>
<p>– 往数据表中插入数据<br><code>insert into pet values(&#39;Puffball&#39;, &#39;Diane&#39;, &#39;hamster&#39;, &#39;f&#39;, &#39;1999-03-30&#39;, NULL);</code><br><code>insert into pet values(&#39;旺财&#39;, &#39;周星驰&#39;, &#39;狗&#39;, &#39;公&#39;, &#39;1990-01-01&#39;, NULL);</code><br>– 还有一种写法,代表我只在name和owner字段上面插入数据,其他皆为NULL/默认值的数据<br><code>insert into pet(name, owner) values (&#39;xx&#39;,&#39;cc&#39;);</code></p>
<hr>
<p>– 删除数据<br>– 语法: delete from tableName where 条件;<br><code>delete from pet where name = &#39;旺财&#39;;</code></p>
<p>– 修改数据<br>– 语法:update tableName set 字段1=值1, 字段2=值2, … where 条件;<br><code>update pet set name = &#39;旺旺财&#39; where owner = &#39;周星驰&#39;;</code></p>
<p>– 删除表<br>– 语法:drop table tableName;<br><code>drop table test;</code></p>
<hr>
<h4 id="mysql-常用数据类型"><a href="#mysql-常用数据类型" class="headerlink" title="mysql 常用数据类型"></a>mysql 常用数据类型</h4><p>见 <a href="https://www.runoob.com/mysql/mysql-data-types.html" target="_blank" rel="noopener">菜鸟教程</a></p>
<p>– 数据类型如何选择?</p>
<ul>
<li>日期选择按照格式</li>
<li>数值和字符串按照大小(常用:int float double char varchar text)</li>
</ul>
<p><strong>注意:</strong> 金钱最好用int/bigint(整数,单位为分,拿出来进行*100换成元),千万不要直接用浮点,会有精度丢失.</p>
<hr>
<h3 id="总结"><a href="#总结" class="headerlink" title="总结:"></a>总结:</h3><ol>
<li>增加: insert</li>
<li>删除: delete</li>
<li>修改: update</li>
<li>查询: select</li>
</ol>
<h2 id="三-mysql建表约束"><a href="#三-mysql建表约束" class="headerlink" title="三.mysql建表约束"></a>三.mysql建表约束</h2><h3 id="主键约束"><a href="#主键约束" class="headerlink" title="主键约束"></a>主键约束</h3><p><strong>1. 主键约束</strong><br>它能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使得该字段<strong>不重复且不为空</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="keyword">user</span>(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span> <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'张三'</span>);</span><br></pre></td></tr></table></figure>
<p>运行<code>desc user</code>后:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">+-------+-------------+------+-----+---------+-------+</span><br><span class="line">| Field | Type        | Null | Key | Default | Extra |</span><br><span class="line">+-------+-------------+------+-----+---------+-------+</span><br><span class="line">| id    | int(11)     | NO   | PRI | NULL    |       |</span><br><span class="line">| name  | varchar(20) | YES  |     | NULL    |       |</span><br><span class="line">+-------+-------------+------+-----+---------+-------+</span><br><span class="line">可以看到id是不可以为NULL 而且 key的值 也变为:PRI(primary)</span><br></pre></td></tr></table></figure>

<p>– 修改表结构,添加主键(比较少用到,要用的时候再百度就行)<br><code>alter table user add primary key(id);</code></p>
<p>– 删除主键(比较少用到,要用的时候再百度就行)<br><code>alter table user drop primary key;</code></p>
<p>– 使用modify修改字段,添加约束(比较少用到,要用的时候再百度就行)<br><code>alter table user modify id int primary key;</code></p>
<p><strong>2. 联合主键</strong><br>联合主键中的每个字段都不为空,且联合的主键加起来不重复</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user2(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    <span class="keyword">password</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    primary <span class="keyword">key</span>(<span class="keyword">id</span>, <span class="keyword">name</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> user2 <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'张三'</span>, <span class="string">'123'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> user2 <span class="keyword">values</span>(<span class="number">2</span>, <span class="string">'张三'</span>, <span class="string">'123'</span>);</span><br></pre></td></tr></table></figure>

<h3 id="自增约束"><a href="#自增约束" class="headerlink" title="自增约束"></a>自增约束</h3><p>– auto_increment<br>– 管控字段,让字段自动增长</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user3(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span> auto_increment,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> user3 (<span class="keyword">name</span>) <span class="keyword">values</span>(<span class="string">'张三'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> user3 (<span class="keyword">name</span>) <span class="keyword">values</span>(<span class="string">'张三'</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> user3;</span><br><span class="line">+<span class="comment">----+--------+</span></span><br><span class="line">| id | name   |</span><br><span class="line">+<span class="comment">----+--------+</span></span><br><span class="line">|  1 | 张三   |</span><br><span class="line">|  2 | 张三   |</span><br><span class="line">+<span class="comment">----+--------+</span></span><br><span class="line">没有自定义id值,但自动生成了id值且自动增长</span><br></pre></td></tr></table></figure>

<h3 id="唯一约束"><a href="#唯一约束" class="headerlink" title="唯一约束"></a>唯一约束</h3><p>– 约束修饰的字段的值不可以重复</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user5(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> user5 <span class="keyword">add</span> <span class="keyword">unique</span>(<span class="keyword">name</span>);</span><br><span class="line"></span><br><span class="line">或:</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user5(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">unique</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">或:</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user5(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    <span class="keyword">unique</span>(<span class="keyword">name</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- unique(id, name),表示两个键加在一起不重复就行</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user5(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    <span class="keyword">unique</span>(<span class="keyword">id</span>, <span class="keyword">name</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> user5 <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'zhangsan'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> user5 <span class="keyword">values</span>(<span class="number">2</span>, <span class="string">'zhangsan'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> user5 <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'lisi'</span>);</span><br></pre></td></tr></table></figure>

<p>– 删除唯一约束<br><code>alter table user5 drop index name;</code></p>
<p>– modify 添加<br><code>alter table user5 modify name varchar(20) unique;</code></p>
<p><strong>总结:</strong></p>
<ol>
<li>主键约束包含了唯一约束.</li>
<li>建表的时候就添加约束</li>
<li>可以使用<code>alter table tableName add unique(字段);</code></li>
<li>可以使用<code>alter table tableName modify 字段名 字段类型 unique;</code></li>
</ol>
<hr>
<p><strong>应用场景:</strong><br>业务需求:设计一张用户注册表,用户姓名必须要用手机号来注册,而且手机号和用户名称都不能为空,那么:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> user_test(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">INT</span> PRIMARY <span class="keyword">KEY</span> AUTO_INCREMENT <span class="keyword">COMMENT</span><span class="string">'主键id'</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span><span class="string">'用户姓名,不能为空'</span>,</span><br><span class="line">    phone_number <span class="built_in">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">UNIQUE</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span><span class="string">'用户手机,不能重复且不能为空'</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>运行 <code>DESCRIBE user_test;</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">+--------------+-------------+------+-----+---------+----------------+</span><br><span class="line">| Field        | Type        | Null | Key | Default | Extra          |</span><br><span class="line">+--------------+-------------+------+-----+---------+----------------+</span><br><span class="line">| id           | int(11)     | NO   | PRI | NULL    | auto_increment |</span><br><span class="line">| name         | varchar(20) | NO   |     | NULL    |                |</span><br><span class="line">| phone_number | int(11)     | NO   | UNI | NULL    |                |</span><br><span class="line">+--------------+-------------+------+-----+---------+----------------+</span><br></pre></td></tr></table></figure>
<p>这样的话就达到了每一个手机号都只能出现一次,达到了每个手机号只能被注册一次.<br>用户姓名可以重复,但是手机号码却不能重复,符合正常的逻辑需求</p>
<hr>
<h3 id="非空约束"><a href="#非空约束" class="headerlink" title="非空约束"></a>非空约束</h3><p>– 修饰的字段不能为空 NULL</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user6(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="literal">NULL</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<p>– 移除非空约束<br><code>alter table user6 modify name varchar(20);</code></p>
<h3 id="默认约束"><a href="#默认约束" class="headerlink" title="默认约束"></a>默认约束</h3><p>– 就是当我们插入字段值的时候,如果没有传值,就会使用默认值<br>– 传了值,就不会使用默认值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> user7(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    age <span class="built_in">int</span> <span class="keyword">default</span> <span class="number">10</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>– 移除默认约束<br><code>alter table user7 modify age int;</code></p>
<h3 id="外键约束"><a href="#外键约束" class="headerlink" title="外键约束"></a>外键约束</h3><p>– 涉及到两个表:父表,子表(主表,副表)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--班级</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> classes(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 学生表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> students(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    class_id <span class="built_in">int</span>,</span><br><span class="line">    <span class="keyword">foreign</span> <span class="keyword">key</span>(class_id) <span class="keyword">references</span> classes(<span class="keyword">id</span>)</span><br><span class="line">);</span><br><span class="line">//foreign :外来  references:参考</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入数据</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> classes <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'一班'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> classes <span class="keyword">values</span>(<span class="number">2</span>, <span class="string">'二班'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> classes <span class="keyword">values</span>(<span class="number">3</span>, <span class="string">'三班'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> classes <span class="keyword">values</span>(<span class="number">4</span>, <span class="string">'四班'</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> students <span class="keyword">values</span>(<span class="number">1001</span>, <span class="string">'张三'</span>, <span class="number">1</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> students <span class="keyword">values</span>(<span class="number">1002</span>, <span class="string">'李四'</span>, <span class="number">2</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> students <span class="keyword">values</span>(<span class="number">1003</span>, <span class="string">'王五'</span>, <span class="number">3</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> students <span class="keyword">values</span>(<span class="number">1004</span>, <span class="string">'赵六'</span>, <span class="number">4</span>);</span><br></pre></td></tr></table></figure>
<p><strong>总结:</strong></p>
<ol>
<li>主表classes中没有的数据,在副表中,是不可以使用的.</li>
<li>主表中的记录被副表引用,则主表中该记录是不可以被删除的.(如副表(students)中引用了4班,则主表(classes)中的4班那条记录不能被删除)</li>
<li>若要想删除,先将副表中的数据删除再删除主表数据</li>
</ol>
<h2 id="四-数据库的三大设计范式"><a href="#四-数据库的三大设计范式" class="headerlink" title="四.数据库的三大设计范式"></a>四.数据库的三大设计范式</h2><h3 id="1-第一范式"><a href="#1-第一范式" class="headerlink" title="1.第一范式"></a>1.第一范式</h3><h4 id="1NF"><a href="#1NF" class="headerlink" title="1NF"></a>1NF</h4><p>– 数据表中的所有字段都是不可分割的原子值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> student2(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    address <span class="built_in">varchar</span>(<span class="number">30</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student2 <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'张三'</span>, <span class="string">'中国广东省罗定市围底镇'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student2 <span class="keyword">values</span>(<span class="number">2</span>, <span class="string">'李四'</span>, <span class="string">'中国广东省罗定市罗平镇'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student2 <span class="keyword">values</span>(<span class="number">3</span>, <span class="string">'王五'</span>, <span class="string">'中国广东省罗定市素龙镇'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 只要字段值还可以继续拆分，就不满足第一范式</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> student3(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    country <span class="built_in">varchar</span>(<span class="number">30</span>),</span><br><span class="line">    province <span class="built_in">varchar</span>(<span class="number">30</span>),</span><br><span class="line">    city <span class="built_in">varchar</span>(<span class="number">30</span>),</span><br><span class="line">    details <span class="built_in">varchar</span>(<span class="number">30</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student3 <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'张三'</span>, <span class="string">'中国'</span>,<span class="string">'广东省'</span>,<span class="string">'罗定市'</span>,<span class="string">'围底镇'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student3 <span class="keyword">values</span>(<span class="number">2</span>, <span class="string">'李四'</span>, <span class="string">'中国'</span>,<span class="string">'广东省'</span>,<span class="string">'罗定市'</span>,<span class="string">'罗平镇'</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student3 <span class="keyword">values</span>(<span class="number">3</span>, <span class="string">'王五'</span>, <span class="string">'中国'</span>,<span class="string">'广东省'</span>,<span class="string">'罗定市'</span>,<span class="string">'素龙镇'</span>);</span><br></pre></td></tr></table></figure>
<p><strong>总结:</strong> 范式设计得越详细，对某些实际操作可能会更好(可以单独对某个字段进行操作,如只选中国,或只选广东省等)，但并非都有好处(如取完整地址时需要一个个拼接)，需要对项目的实际情况进行设定。</p>
<h3 id="2-第二范式"><a href="#2-第二范式" class="headerlink" title="2.第二范式"></a>2.第二范式</h3><h4 id="2NF"><a href="#2NF" class="headerlink" title="2NF"></a>2NF</h4><p>– 必须在<strong>满足第一范式的前提下</strong>,第二范式要求,除主键外的每一列都必须<strong>完全依赖于主键(依赖就是相关的意思)</strong>.<br>– 如果要出现不完全依赖,只可能发生在<strong>联合主键</strong>的情况下.</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 订单表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> myOrder(</span><br><span class="line">    product_id <span class="built_in">int</span>,</span><br><span class="line">    customer_id <span class="built_in">int</span>,</span><br><span class="line">    product_name <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    customer_name <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    primary <span class="keyword">key</span>(product_id, customer_id)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>实际上，在这张订单表中，<code>product_name</code> 只依赖于 <code>product_id</code> ，<code>customer_name</code> 只依赖于 <code>customer_id</code> 。也就是说，<code>product_name</code> 和 <code>customer_id</code> 是没有关系的，<code>customer_name</code> 和 <code>product_id</code> 也是没有关系的。</p>
<p>– 存在问题:除主键外的其他列,只依赖于主键的部分字段<br>– 解决:拆表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> myOrder(</span><br><span class="line">    order_id <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    product_id <span class="built_in">int</span>,</span><br><span class="line">    customer_id <span class="built_in">int</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> product(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    ame <span class="built_in">varchar</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> customer(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>– 分成三个表之后,就满足了第二范式的设计,因为<code>myOrder</code>表中的<code>product_id</code>和<code>customer_id</code>完全依赖于主键<code>order_id</code>,而<code>product</code>和<code>customer</code>中的其他字段也完全依赖于主键,满足第二范式的设计.</p>
<h3 id="3-第三范式"><a href="#3-第三范式" class="headerlink" title="3.第三范式"></a>3.第三范式</h3><h4 id="3NF"><a href="#3NF" class="headerlink" title="3NF"></a>3NF</h4><p>– 必须在<strong>满足第二范式的前提下</strong>,除开主键外的其他列之间不能有传递依赖关系.</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> myOrder(</span><br><span class="line">    order_id <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    product_id <span class="built_in">int</span>,</span><br><span class="line">    customer_id <span class="built_in">int</span>,</span><br><span class="line">    customer_phone <span class="built_in">varchar</span>(<span class="number">15</span>)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>– 存在问题: 在myOrder中,<code>customer_phone</code>依赖于<code>order_id</code>,但它还依赖于<code>customer_id</code>,而<code>customer_id</code>又依赖于<code>order_id</code>,所以存在传递依赖,不满足第三范式的要求.<br>– 解决:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> customer(</span><br><span class="line">    <span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span>,</span><br><span class="line">    <span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    phone <span class="built_in">varchar</span>(<span class="number">15</span>)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>– 修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！</p>
<h2 id="参考资料："><a href="#参考资料：" class="headerlink" title="参考资料："></a>参考资料：</h2><ol>
<li>B站视频<a href="https://www.bilibili.com/video/BV1Vt411z7wy" target="_blank" rel="noopener">“一天学会 MySQL 数据库”</a></li>
<li><a href="https://cc-51760.oss-cn-beijing.aliyuncs.com/MYSQL%E7%AC%94%E8%AE%B0.zip" target="_blank" rel="noopener">大佬笔记1</a></li>
<li><a href="https://github.com/hjzCy/sql_node/blob/master/mysql/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0.md" target="_blank" rel="noopener">大佬笔记2</a></li>
</ol>

          
            <div class='article_footer'>
              
                
  
    
    



  

  
    
    



  

  
    
    

<section class="widget copyright  desktop mobile">
  <div class='content'>
    
      <blockquote>
        
          
            <p>本文由CyChan创作，转载请注明出处</p>

          
        
          
            <p>博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议</p>

          
        
          
            <p>本文永久链接是：<a href=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/>https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/</a></p>
          
        
      </blockquote>
    
  </div>
</section>

  

  
    
    

<section class="widget qrcode  desktop mobile">
  

  <div class='content article-entry'>
    
      
        <div class='fancybox'><img src='https://gitee.com/CyChan811/BlogImage/raw/master/img/20200704103702.png'
        
          height='128px'
        ></div>
      
    
  </div>
</section>

  


              
            </div>
          
        </div>
        
          


  <section class='meta' id="footer-meta">
    <div class='new-meta-box'>
      
        
          <div class="new-meta-item date" itemprop="dateUpdated" datetime="2020-07-06T22:36:54+08:00">
  <a class='notlink'>
    <i class="fas fa-edit fa-fw" aria-hidden="true"></i>
    <p>更新于：2020年7月6日</p>
  </a>
</div>

        
      
        
          
  
  <div class="new-meta-item meta-tags"><a class="tag" href="/tags/MySQL/" rel="nofollow"><i class="fas fa-hashtag fa-fw" aria-hidden="true"></i><p>MySQL</p></a></div>


        
      
        
          

        
      
        
          
  <div class="new-meta-item share -mob-share-list">
  <div class="-mob-share-list share-body">
    
      
        <a class="-mob-share-qq" title="" rel="external nofollow noopener noreferrer"
          
          href="http://connect.qq.com/widget/shareqq/index.html?url=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/&title=MySQL笔记(1):终端基本操作 - CyChan's Blog&summary=一.登录和退出数据库服务器
登录:$ mysql -uroot -p123456  
退出:exit;"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/qq.png">
          
        </a>
      
    
      
        <a class="-mob-share-qzone" title="" rel="external nofollow noopener noreferrer"
          
          href="https://sns.qzone.qq.com/cgi-bin/qzshare/cgi_qzshare_onekey?url=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/&title=MySQL笔记(1):终端基本操作 - CyChan's Blog&summary=一.登录和退出数据库服务器
登录:$ mysql -uroot -p123456  
退出:exit;"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/qzone.png">
          
        </a>
      
    
      
        <a class="-mob-share-weibo" title="" rel="external nofollow noopener noreferrer"
          
          href="http://service.weibo.com/share/share.php?url=https://cychan811.gitee.io/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-1-%E7%BB%88%E7%AB%AF%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C/&title=MySQL笔记(1):终端基本操作 - CyChan's Blog&summary=一.登录和退出数据库服务器
登录:$ mysql -uroot -p123456  
退出:exit;"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/weibo.png">
          
        </a>
      
    
  </div>
</div>



        
      
    </div>
  </section>


        
        
          <div class="prev-next">
            
              <a class='prev' href='/2020/07/04/MySQL%E7%AC%94%E8%AE%B0-2-%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0/'>
                <p class='title'><i class="fas fa-chevron-left" aria-hidden="true"></i>MySQL笔记(2):查询练习</p>
                <p class='content'>mysql查询练习创建数据表
新建数据库:create database select_test;选择数据库:use select_test;
– 学生表– Student– 学号– 姓名– 性...</p>
              </a>
            
            
              <a class='next' href='/2020/07/04/PicGo-gitee%E6%90%AD%E5%BB%BA%E4%B8%AA%E4%BA%BA%E5%85%8D%E8%B4%B9%E5%9B%BE%E5%BA%8A/'>
                <p class='title'>PicGo+gitee搭建个人免费图床<i class="fas fa-chevron-right" aria-hidden="true"></i></p>
                <p class='content'>在用markdown写文档时，总有需要插入图片的时候，但图片文件存储在本地时可不方便分享，因此可以通过PicGo+gitee的方式搭建个人免费图床，将图片存储在线上。操作步骤如下：
一. 下载P...</p>
              </a>
            
          </div>
        
      </section>
    </article>
  

  
    <!-- 显示推荐文章和评论 -->



  <article class="post white-box reveal comments shadow">
    <section class="article typo">
      <p ct><i class='fas fa-comments'></i> 评论</p>
      
      
      
      
      
      
        <section id="comments">
          <div id="valine_container" class="valine_thread">
            <i class="fas fa-cog fa-spin fa-fw fa-2x"></i>
          </div>
        </section>
      
      
    </section>
  </article>


  




<!-- 根据页面mathjax变量决定是否加载MathJax数学公式js -->



  <script>
    window.subData = {
      title: 'MySQL笔记(1):终端基本操作',
      tools: true
    }
  </script>


</div>
<aside class='l_side'>
  
  

  
    
    



  <section class="widget toc-wrapper shadow desktop mobile" id="toc-div" >
    
  <header>
    
      <i class="fas fa-list fa-fw" aria-hidden="true"></i><span class='name'>本文目录</span>
    
  </header>


    <div class='content'>
        <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#一-登录和退出数据库服务器"><span class="toc-text">一.登录和退出数据库服务器</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#二-基本语法"><span class="toc-text">二.基本语法</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#mysql-常用数据类型"><span class="toc-text">mysql 常用数据类型</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#总结"><span class="toc-text">总结:</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#三-mysql建表约束"><span class="toc-text">三.mysql建表约束</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#主键约束"><span class="toc-text">主键约束</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#自增约束"><span class="toc-text">自增约束</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#唯一约束"><span class="toc-text">唯一约束</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#非空约束"><span class="toc-text">非空约束</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#默认约束"><span class="toc-text">默认约束</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#外键约束"><span class="toc-text">外键约束</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#四-数据库的三大设计范式"><span class="toc-text">四.数据库的三大设计范式</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-第一范式"><span class="toc-text">1.第一范式</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#1NF"><span class="toc-text">1NF</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-第二范式"><span class="toc-text">2.第二范式</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#2NF"><span class="toc-text">2NF</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-第三范式"><span class="toc-text">3.第三范式</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3NF"><span class="toc-text">3NF</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#参考资料："><span class="toc-text">参考资料：</span></a></li></ol>
    </div>
  </section>


  


</aside>


  
  <footer class="clearfix">
    <br><br>
    
      
        <div class="aplayer-container">
          

  
    <meting-js
      theme='#1BCDFC'
      autoplay='false'
      volume='0.7'
      loop='all'
      order='random'
      fixed='false'
      list-max-height='340px'
      server='netease'
      type='playlist'
      id='2985312517'
      list-folded='true'>
    </meting-js>
  


        </div>
      
    
      
        <br>
        <div class="social-wrapper">
          
            
              <a href="mailto:1342736410@qq.com"
                class="social fas fa-envelope flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="https://github.com/CyChan811"
                class="social fab fa-github flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="http://wpa.qq.com/msgrd?v=3&amp;uin=1342736410&amp;site=qq&amp;menu=yes"
                class="social fab fa-qq flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="http://music.163.com/playlist?id=2985312517&amp;userid=1980577201"
                class="social fas fa-headphones-alt flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
        </div>
      
    
      
        <div><p>博客内容遵循 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" target="_blank" rel="noopener">署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议</a></p>
        </div>
      
    
      
        本站使用
        <a href="https://volantis.js.org/" target="_blank" class="codename">volantis</a>
        作为主题，总访问量为
          <span id="busuanzi_value_site_pv"><i class="fas fa-circle-notch fa-spin fa-fw" aria-hidden="true"></i></span>
          次
      
    
      
        
      
    
      
      <br>
        <span id="timeDate"></span><span id="times"></span>
        <div class='copyright'>
          <a href="https://cychan811.gitee.io">Copyright © 2017-2020 CyChan</a>
        </div>
      
    
    
  </footer>


      <script>
      var now = new Date(); 
      function createtime() { 
          var grt= new Date("07/01/2020 12:00:00");//在此处修改你的建站时间，格式：月/日/年 时:分:秒
          now.setTime(now.getTime()+250); 
          days = (now - grt ) / 1000 / 60 / 60 / 24; dnum = Math.floor(days); 
          hours = (now - grt ) / 1000 / 60 / 60 - (24 * dnum); hnum = Math.floor(hours); 
          if(String(hnum).length ==1 ){hnum = "0" + hnum;} minutes = (now - grt ) / 1000 /60 - (24 * 60 * dnum) - (60 * hnum); 
          mnum = Math.floor(minutes); if(String(mnum).length ==1 ){mnum = "0" + mnum;} 
          seconds = (now - grt ) / 1000 - (24 * 60 * 60 * dnum) - (60 * 60 * hnum) - (60 * mnum); 
          snum = Math.round(seconds); if(String(snum).length ==1 ){snum = "0" + snum;} 
          document.getElementById("timeDate").innerHTML = "本站已安全运行 "+dnum+" 天 "+ hnum + " 小时 " + mnum + " 分 " + snum + " 秒"; 
      } 
        setInterval("createtime()",250);
     </script>
<script>setLoadingBarProgress(80);</script>


      <script>setLoadingBarProgress(60);</script>
    </div>
    <a class="s-top fas fa-arrow-up fa-fw" href='javascript:void(0)'></a>
  </div>
  
<script src="https://cdn.jsdelivr.net/npm/jquery@3.4/dist/jquery.min.js"></script>


  <script>
    
    var SEARCH_SERVICE = "hexo" || "hexo";
    var ROOT = "/" || "/";
    if (!ROOT.endsWith('/')) ROOT += '/';
  </script>





  <script async src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2/js/instant_page.js" type="module" defer integrity="sha384-OeDn4XE77tdHo8pGtE1apMPmAipjoxUQ++eeJa6EtJCfHlvijigWiJpD7VDPWXV1"></script>


  <script src="https://cdn.jsdelivr.net/npm/scrollreveal@4.0.6/dist/scrollreveal.min.js"></script>
  <script type="text/javascript">
    $(function() {
      ScrollReveal().reveal('.l_main .reveal', {
        distance: '8px',
        duration: '800',
        interval: '100',
        scale: '1'
      });
    });
  </script>


  
<script src="https://cdn.jsdelivr.net/npm/node-waves@0.7.6/dist/waves.min.js"></script>

  <script type="text/javascript">
    $(function() {
      Waves.attach('.flat-btn', ['waves-button']);
      Waves.attach('.float-btn', ['waves-button', 'waves-float']);
      Waves.attach('.float-btn-light', ['waves-button', 'waves-float', 'waves-light']);
      Waves.attach('.flat-box', ['waves-block']);
      Waves.attach('.float-box', ['waves-block', 'waves-float']);
      Waves.attach('.waves-image');
      Waves.init();
    });
  </script>


  <script defer src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-busuanzi@2.3/js/busuanzi.pure.mini.js"></script>



  
  
  
    
<script src="https://cdn.jsdelivr.net/npm/jquery-backstretch@2.1.18/jquery.backstretch.min.js"></script>

    <script type="text/javascript">
      $(function(){
        var imgs=["https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/41F215B9-261F-48B4-80B5-4E86E165259E.jpeg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/BBC19066-E176-47C2-9D22-48C81EE5DF6B.jpeg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/B18FCBB3-67FD-48CC-B4F3-457BA145F17A.jpeg", "https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=3799510479,4073126189&fm=26&gp=0.jpg", "https://ss3.bdstatic.com/70cFv8Sh_Q1YnxGkpoWK1HF6hhy/it/u=2883764232,1534773763&fm=26&gp=0.jpg", "https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1593783783483&di=3ad7e4713031f3e60c3a8bab2c2aae02&imgtype=0&src=http%3A%2F%2Fcz-video-photo.oss-cn-beijing.aliyuncs.com%2F20191015%2F7d47db92bbe2627ecd035976a61463cb_00001.jpg", "https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1593783631802&di=0616aa53378b2de7816f786a97c75bfe&imgtype=0&src=http%3A%2F%2Fpic1.win4000.com%2Fwallpaper%2F2018-01-10%2F5a55c62f9a142.jpg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/00E0F0ED-9F1C-407A-9AA6-545649D919F4.jpeg", "https://cdn.jsdelivr.net/gh/zyoushuo/Blog/images/bj1.jpg", "https://gitee.com/CyChan811/BlogImage/raw/master/img/1 (2).png", "https://gitee.com/CyChan811/BlogImage/raw/master/img/t01e2fa7120d00d62da.jpg", "https://gitee.com/CyChan811/BlogImage/raw/master/img/1 (19).jpg"];
        if ('true' == 'true') {
          function shuffle(arr){
            /*From countercurrent-time*/
            var n = arr.length;
            while(n--) {
              var index = Math.floor(Math.random() * n);
              var temp = arr[index];
              arr[index] = arr[n];
              arr[n] = temp;
            }
          }
          shuffle(imgs);
        }
        if ('') {
          $('').backstretch(
            imgs,
          {
            duration: "20000",
            fade: "1500"
          });
        } else {
          $.backstretch(
            imgs,
          {
            duration: "20000",
            fade: "1500"
          });
        }
      });
    </script>
  



  
    
<script src="https://cdn.jsdelivr.net/npm/aplayer@1.10/dist/APlayer.min.js"></script>

  
    
<script src="https://cdn.jsdelivr.net/npm/meting@2.0/dist/Meting.min.js"></script>

  









  
    
<script src="https://cdn.jsdelivr.net/gh/HCLonely/Valine@latest/dist/Valine.min.js"></script>

  
  <script>
  var GUEST_INFO = ['nick','mail','link'];
  var meta = 'nick,mail,link'.split(',').filter(function(item){
    return GUEST_INFO.indexOf(item) > -1
  });
  var REQUIRED_FIELDS = ['nick','mail','link'];
  var requiredFields = 'nick,mail'.split(',').filter(function(item){
    return REQUIRED_FIELDS.indexOf(item) > -1
  });
  var valine = new Valine();
  var master = 'f03ef4cd1728b39a2b1d24cb0c7c4ac8'.split(',');
  function emoji(path, idx, ext) {
      return path + "/" + path + "-" + idx + "." + ext;
  }
  var emojiMaps = {};
  for (var i = 1; i <= 54; i++) {
    emojiMaps['tieba-' + i] = emoji('tieba', i, 'png');
  }
  for (var i = 1; i <= 101; i++) {
    emojiMaps['qq-' + i] = emoji('qq', i, 'gif');
  }
  for (var i = 1; i <= 116; i++) {
    emojiMaps['aru-' + i] = emoji('aru', i, 'gif');
  }
  for (var i = 1; i <= 125; i++) {
    emojiMaps['twemoji-' + i] = emoji('twemoji', i, 'png');
  }
  for (var i = 1; i <= 4; i++) {
    emojiMaps['weibo-' + i] = emoji('weibo', i, 'png');
  }
  valine.init({
    el: '#valine_container',
    meta: meta,
    
    appId: "COclWTHE9FOx0Kp5LQxypRuw-gzGzoHsz",
    appKey: "IFkkcvnaeUWqUUYoWqCJ5hUY",
    placeholder: "ヾﾉ≧∀≦)o来啊，快活啊!~",
    pageSize:'10',
    avatar:'robohash',
    lang:'zh-cn',
    visitor: 'true',
    highlight: 'true',
    mathJax: 'false',
    enableQQ: 'true',
    requiredFields: requiredFields,
    emojiCDN: 'https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/emoji/valine/',
    emojiMaps: emojiMaps,
    master: master
  })
  </script>





  
<script src="/js/app.js"></script>



  
<script src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2.6.5/js/search.js"></script>



  
<script src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2/js/comment_typing.js"></script>






<!-- 复制 -->

  <script src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js"></script>
<script>
  function wait(callback, seconds) {
    var timelag = null;
    timelag = window.setTimeout(callback, seconds);
  }
  !function (e, t, a) {
    var initCopyCode = function(){
      var copyHtml = '';
      copyHtml += '<button class="btn-copy" data-clipboard-snippet="">';
      copyHtml += '<i class="fas fa-copy"></i><span>COPY</span>';
      copyHtml += '</button>';
      $(".highlight .code pre").before(copyHtml);
      $(".article pre code").before(copyHtml);
      var clipboard = new ClipboardJS('.btn-copy', {
        target: function(trigger) {
          return trigger.nextElementSibling;
        }
      });
      clipboard.on('success', function(e) {
        let $btn = $(e.trigger);
        $btn.addClass('copied');
        let $icon = $($btn.find('i'));
        $icon.removeClass('fa-copy');
        $icon.addClass('fa-check-circle');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPIED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('fa-check-circle');
          $icon.addClass('fa-copy');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
      clipboard.on('error', function(e) {
        e.clearSelection();
        let $btn = $(e.trigger);
        $btn.addClass('copy-failed');
        let $icon = $($btn.find('i'));
        $icon.removeClass('fa-copy');
        $icon.addClass('fa-times-circle');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPY FAILED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('fa-times-circle');
          $icon.addClass('fa-copy');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
    }
    initCopyCode();
  }(window, document);
</script>




<!-- fancybox -->
<script src="https://cdn.jsdelivr.net/gh/fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.js"></script>
<script>
  function pjax_fancybox() {
    $(".article-entry").find("img").not('.inline').not('a img').each(function () { //渲染 fancybox
      var element = document.createElement("a"); // a 标签
      $(element).attr("pjax-fancybox", "");  // 过滤 pjax
      $(element).attr("href", $(this).attr("src"));
      if ($(this).attr("data-original")) {
        $(element).attr("href", $(this).attr("data-original"));
      }
      $(element).attr("data-fancybox", "images");
      var caption = "";   // 描述信息
      if ($(this).attr('alt')) {  // 标准 markdown 描述信息
        $(element).attr('data-caption', $(this).attr('alt'));
        caption = $(this).attr('alt');
      }
      var div = document.createElement("div");
      $(div).addClass("fancybox");
      $(this).wrap(div); // 最外层套 div ，其实主要作用还是 class 样式
      var span = document.createElement("span");
      $(span).addClass("image-caption");
      $(span).text(caption); // 加描述
      $(this).after(span);  // 再套一层描述
      $(this).wrap(element);  // 最后套 a 标签
    })
    $(".article-entry").find("img").fancybox({
      selector: '[data-fancybox="images"]',
      hash: false,
      loop: false,
      closeClick: true,
      helpers: {
        overlay: {closeClick: true}
      },
      buttons: [
        "zoom",
        "close"
      ]
    });
  };
  $(function () {
    pjax_fancybox();
  });
</script>





  <script>setLoadingBarProgress(100);</script>
  <script src="https://cdn.jsdelivr.net/gh/zyoushuo/Blog/hexo/js/mouse_click.js"></script>
</body>
</html>
